package com.css.fxfzysx.modules.cztDataMange.utils;

import cn.hutool.core.date.DateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;

import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.lang.reflect.Method;
import java.util.*;
import java.util.Map.Entry;

public class ExcelUtils {


    /**
     * @param dataList        数据
     * @param headNameMap     标题
     * @param type            类型 1 xls 2 xlsx
     * @param mergeIndex      需要合并的列 从1开始  0是序号
     * @param benchmarkColumn 基准列（就是以那一列为标准来决定合不合并 以下面的例子为说明 我输入1 就是以订单号为准 订单号合并才决定合并）
     * @return
     * @throws Exception
     */
    public static byte[] toExcelMergeCell(List<?> dataList, Map<String, String> headNameMap, int type, int[] mergeIndex, Integer benchmarkColumn) throws Exception {
        Workbook workbook;
        if (type == 1) {
            workbook = new XSSFWorkbook();
        } else if (type == 2) {
            workbook = new SXSSFWorkbook();
        } else {
            workbook = new HSSFWorkbook();
        }
        List<Method> methodList = null;
        Sheet sheet = workbook.createSheet("数据列表");
        int index = sheet.getPhysicalNumberOfRows();
        for (int i = 0; i < dataList.size(); i++) {
            Object object = dataList.get(i);
            if (methodList == null) {
                Method[] methods = object.getClass().getMethods();
                methodList = new ArrayList<>();
                Row rowHead = sheet.createRow(index);
                rowHead.createCell(0).setCellValue("序号");
                Iterator<Entry<String, String>> iterator = headNameMap.entrySet().iterator();
                int c = 1;
                while (iterator.hasNext()) {
                    Entry<String, String> entry = iterator.next();
                    for (int m = 0; m < methods.length; m++) {
                        if (methods[m].getName().toLowerCase().equals(("get" + entry.getKey()).toLowerCase())) {
                            methodList.add(methods[m]);
                            Cell cell = rowHead.createCell(c);
                            setCellValue(cell, entry.getValue());
                            c++;
                        }
                        if (methods[m].getName().toLowerCase().equals(("getlist"))) {
                            Object invoke = methods[m].invoke(object);
                        }
                    }
                }
            }
            Row row = sheet.createRow(index + 1);
            row.createCell(0).setCellValue(i + 1);
            for (int m = 0; m < methodList.size(); m++) {
                Object value = methodList.get(m).invoke(object);
                Cell cell = row.createCell(m + 1);
                Object textValue = getValue(value);
                setCellValue(cell, textValue);

            }
            index++;
        }
        String str = null;
        int strBeginIndex;
        int strEndIndex;
        int j;
        int start;
        int end = 0;
        Map<Integer, Integer> benchmarkMap = new LinkedHashMap<>();
        for (int i = 0; i < mergeIndex.length; i++) {
            j = 0;
            start = 0;
            strBeginIndex = 0;
            strEndIndex = 0;
            if (mergeIndex[i] >= 11 && mergeIndex[i] <= 12) {
                for (Integer endIndex : benchmarkMap.keySet()) {
                    CellRangeAddress region = new CellRangeAddress(benchmarkMap.get(endIndex), endIndex, mergeIndex[i], mergeIndex[i]);
                    sheet.addMergedRegion(region);
                }
            }
            for (Row row : sheet) {
                if (j == 0) {
                    j++;
                    continue;
                }

                if (Objects.isNull(str)) {

                    if (Objects.nonNull(row.getCell(mergeIndex[i]))) {
                        str = row.getCell(mergeIndex[i]).getStringCellValue();
                    } else {
                        continue;
                    }
                    if (str.equals(sheet.getRow(2).getCell(1).getStringCellValue())) {
                        strBeginIndex = row.getRowNum();
                    }

                } else if (str.equals(new DataFormatter().formatCellValue(row.getCell(mergeIndex[i])))) {
                    if (strBeginIndex == 0) {
                        strBeginIndex = sheet.getRow(j - 1).getRowNum();
                    }
                    strEndIndex = sheet.getLastRowNum();
                    end = strEndIndex;
                    if (sheet.getLastRowNum() == j) {
                        //末尾合并
                        if (mergeIndex[i] == benchmarkColumn) {
                            CellRangeAddress region = new CellRangeAddress(strBeginIndex, strEndIndex, mergeIndex[i], mergeIndex[i]);
                            sheet.addMergedRegion(region);
                        } else if (!benchmarkMap.isEmpty() && null != benchmarkColumn && mergeIndex[i] > benchmarkColumn) {
                            consolidatedColumn(benchmarkMap, strBeginIndex, strEndIndex, mergeIndex[i], end, sheet);
                        }
                        if (mergeIndex[i] == 1) {
                            benchmarkMap.put(strEndIndex, strBeginIndex);
                        }
                    }
                } else if (!str.equals(new DataFormatter().formatCellValue(row.getCell(mergeIndex[i])))) {
                    strEndIndex = row.getRowNum();
                    if (start == 0 && strBeginIndex > 0 && strEndIndex > 0) {
                        //首行合并
                        strEndIndex = strEndIndex - 1;
                        end = strEndIndex;
                        if (mergeIndex[i] == benchmarkColumn) {
                            CellRangeAddress region = new CellRangeAddress(strBeginIndex, strEndIndex, mergeIndex[i], mergeIndex[i]);
                            sheet.addMergedRegion(region);
                        } else if (!benchmarkMap.isEmpty() && null != benchmarkColumn && mergeIndex[i] > benchmarkColumn) {
                            consolidatedColumn(benchmarkMap, strBeginIndex, strEndIndex, mergeIndex[i], end, sheet);
                        }
                        if (mergeIndex[i] == 1) {
                            benchmarkMap.put(strEndIndex, strBeginIndex);
                        }
                        strBeginIndex = 0;
                        start = 1;
                    } else if (strBeginIndex > 0 && strEndIndex > 0) {
                        //中间行合并
                        strEndIndex = strEndIndex - 1;
                        end = strEndIndex;
                        if (mergeIndex[i] == benchmarkColumn) {
                            CellRangeAddress region = new CellRangeAddress(strBeginIndex, strEndIndex, mergeIndex[i], mergeIndex[i]);
                            sheet.addMergedRegion(region);
                        } else if (!benchmarkMap.isEmpty() && null != benchmarkColumn && mergeIndex[i] > benchmarkColumn) {
                            consolidatedColumn(benchmarkMap, strBeginIndex, strEndIndex, mergeIndex[i], end, sheet);
                        }
                        if (mergeIndex[i] == 1) {
                            benchmarkMap.put(strEndIndex, strBeginIndex);
                        }
                        strBeginIndex = 0;

                    }
                    str = new DataFormatter().formatCellValue(row.getCell(mergeIndex[i]));
                }
                j++;
            }
        }
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        workbook.write(baos);
        workbook.close();
        return baos.toByteArray();
    }

    private static void consolidatedColumn(Map<Integer, Integer> benchmarkMap, Integer strBeginIndex, Integer strEndIndex, Integer mergeIndex, Integer end, Sheet sheet) {
        for (Integer endIndex : benchmarkMap.keySet()) {
            if (strBeginIndex >= benchmarkMap.get(endIndex) && strEndIndex >= endIndex && strBeginIndex < endIndex) {
                strEndIndex = endIndex;
                if (strBeginIndex < strEndIndex) {
                    CellRangeAddress region = new CellRangeAddress(strBeginIndex, strEndIndex, mergeIndex, mergeIndex);
                    sheet.addMergedRegion(region);
                }
                strBeginIndex = strEndIndex + 1;
                strEndIndex = end;
            } else if (strBeginIndex >= benchmarkMap.get(endIndex) && strEndIndex <= endIndex && strBeginIndex < endIndex) {
                if (strBeginIndex < strEndIndex) {
                    CellRangeAddress region = new CellRangeAddress(strBeginIndex, strEndIndex, mergeIndex, mergeIndex);
                    sheet.addMergedRegion(region);
                }
                strBeginIndex = strEndIndex + 1;
                strEndIndex = end;
            } else if (strBeginIndex < benchmarkMap.get(endIndex) && strEndIndex >= endIndex && strBeginIndex < endIndex) {
                strBeginIndex = benchmarkMap.get(endIndex);
                strEndIndex = endIndex;
                if (strBeginIndex < strEndIndex) {
                    CellRangeAddress region = new CellRangeAddress(strBeginIndex, strEndIndex, mergeIndex, mergeIndex);
                    sheet.addMergedRegion(region);
                }
                strBeginIndex = strEndIndex + 1;
                strEndIndex = end;
            } else if (strBeginIndex <= benchmarkMap.get(endIndex) && strEndIndex <= endIndex && strBeginIndex < endIndex) {
                if (!isSection(benchmarkMap, strBeginIndex)) {
                    strBeginIndex = benchmarkMap.get(endIndex);
                    if (strBeginIndex < strEndIndex) {
                        CellRangeAddress region = new CellRangeAddress(strBeginIndex, strEndIndex, mergeIndex, mergeIndex);
                        sheet.addMergedRegion(region);

                    }
                    strBeginIndex = strEndIndex + 1;
                    strEndIndex = end;
                }

            }
        }
    }

    private static Object getValue(Object value) {
        Object textValue = "";
        if (value != null) {
            if (value instanceof Boolean) {
                textValue = (Boolean) value ? "是" : "否";
            } else if (value instanceof Date) {
                textValue = DateUtil.format((Date) value, "yyyy-MM-dd HH:mm:ss");
            } else if (value instanceof String) {
                String val = (String) value;
                textValue = Objects.isNull(val) || "null".equalsIgnoreCase(val) ? "" : val;
            } else {
                textValue = value;
            }
        }
        return textValue;
    }

    private static boolean isSection(Map<Integer, Integer> benchmarkMap, Integer value) {
        for (Integer integer : benchmarkMap.keySet()) {
            if (value >= benchmarkMap.get(integer) && value <= integer) {
                return true;
            }
        }
        return false;
    }

    private static void setCellValue(Cell cell, Object value) {
        if (value != null) {
            if (value instanceof Integer) {
                cell.setCellValue((Integer) value);
            } else if (value instanceof Boolean) {
                Boolean booleanValue = (Boolean) value;
                cell.setCellValue(booleanValue);
            } else if (value instanceof Date) {
                Date dateValue = (Date) value;
                cell.setCellValue(dateValue);
            } else if (value instanceof Float) {
                Float floatValue = (Float) value;
                cell.setCellValue(floatValue);
            } else if (value instanceof Double) {
                Double doubleValue = (Double) value;
                cell.setCellValue(doubleValue);
            } else if (value instanceof Long) {
                Long longValue = (Long) value;
                cell.setCellValue(longValue);
            } else {
                cell.setCellValue(value.toString());
            }
        }
    }
}